package sample;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.TableView;
import javafx.scene.paint.Color;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

    public static ObservableList readExcel(File xlsFile, ExcelTypeEnum e) throws IOException {
        InputStream is = new FileInputStream(xlsFile);
        Workbook workbook;
        if (xlsFile.toString().toLowerCase().endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else {
            workbook = new XSSFWorkbook(is);
        }

        int numberOfSheets = workbook.getNumberOfSheets();
        List<BaseInfo> li = new ArrayList<>();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            int cells = temp.getPhysicalNumberOfCells();

            switch (e){
                case 母表一:
                    for (int row = 1; row < rowNumbers; row++) {
                        Row r = sheet.getRow(row);
                        Mother1Info bi = new Mother1Info();
                        bi.setMonth1(getCellValue(r.getCell(0)));
                        bi.setOutDate1(getCellValue(r.getCell(1)));
                        bi.setOutNumber1(getCellValue(r.getCell(2)));
                        bi.setPrincipal1(getCellValue(r.getCell(3)));
                        bi.setClient1(getCellValue(r.getCell(4)));
                        bi.setProductName1(getCellValue(r.getCell(5)));
                        bi.setProductSpecification1(getCellValue(r.getCell(6)));
                        bi.setSupplier1(getCellValue(r.getCell(7)));
                        bi.setManufacturer1(getCellValue(r.getCell(8)));
                        bi.setBatchNo1(getCellValue(r.getCell(9)));
                        bi.setExpiryDate1(getCellValue(r.getCell(10)));
                        bi.setUnit1(getCellValue(r.getCell(11)));
                        bi.setQuantity1(getCellValue(r.getCell(12)));
                        bi.setCostPrice1(getCellValue(r.getCell(13)));
                        bi.setCostMoney1(getCellValue(r.getCell(14)));
                        bi.setSellPrice1(getCellValue(r.getCell(15)));
                        bi.setSellMoney1(getCellValue(r.getCell(16)));
                        bi.setInvoicePrice1(getCellValue(r.getCell(17)));
                        bi.setInvoiceMoney1(getCellValue(r.getCell(18)));
                        bi.setRebateManufacturer1(getCellValue(r.getCell(19)));
                        bi.setRebateManufacturerMoney1(getCellValue(r.getCell(20)));
                        bi.setClinical1(getCellValue(r.getCell(21)));
                        bi.setClinicalMoney1(getCellValue(r.getCell(22)));
                        li.add(bi);
                    }
                    break;
                case 母表二: {
                    for (int row = 1; row < rowNumbers; row++) {
                        Row r = sheet.getRow(row);
                        Mother2Info bi = new Mother2Info();
                        bi.setMonth2(getCellValue(r.getCell(0)));
                        bi.setOutDate2(getCellValue(r.getCell(1)));
                        bi.setOutNumber2(getCellValue(r.getCell(2)));
                        bi.setClient2(getCellValue(r.getCell(3)));
                        bi.setProductName2(getCellValue(r.getCell(4)));
                        bi.setProductSpecification2(getCellValue(r.getCell(5)));
                        bi.setSupplier2(getCellValue(r.getCell(6)));
                        bi.setManufacturer2(getCellValue(r.getCell(7)));
                        bi.setBatchNo2(getCellValue(r.getCell(8)));
                        bi.setExpiryDate2(getCellValue(r.getCell(9)));
                        bi.setUnit2(getCellValue(r.getCell(10)));
                        bi.setQuantity2(getCellValue(r.getCell(11)));
                        bi.setCostPrice2(getCellValue(r.getCell(12)));
                        bi.setCostMoney2(getCellValue(r.getCell(13)));
                        bi.setInvoicePrice2(getCellValue(r.getCell(14)));
                        bi.setInvoiceMoney2(getCellValue(r.getCell(15)));
                        li.add(bi);
                    }
                    break;
                }
            }



            ObservableList<BaseInfo> result = FXCollections.observableArrayList(li);
            return result;
        }
    return null;

    }

    public static String getCellValue(Cell cell) {
        String value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case FORMULA:
                    // cell.getCellFormula();
                    try {
                        value = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        value = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        String pattern = "yyyy-MM-dd";
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
                        value = simpleDateFormat.format(date);
                    } else {
                        double dValue = cell.getNumericCellValue();
                        DecimalFormat df = new DecimalFormat("0");
                        value = df.format(dValue);
                    }

                    break;
                case STRING:
                    value = String.valueOf(cell.getRichStringCellValue());
                    break;
            }
        }

        return value;
    }

    public static void exportExcel(TableView<Mother1Info> mother1,TableView<Mother2Info> mother2, String fileName){
        // Declare a work sheet
        HSSFWorkbook workbook = new HSSFWorkbook();
        // Generate a form
        HSSFSheet sheet = workbook.createSheet("销售明细");
        // Set the table for 15 byte default column width
        sheet.setDefaultColumnWidth((short) 15);
        // Create a style
        HSSFCellStyle style = workbook.createCellStyle();
        // The style settings
//        style.setFillForegroundColor(HSSFColor.index);
        style.setFillPattern(FillPatternType.ALT_BARS.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // Create a font
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);

        // The font applied to the current style
        style.setFont(font);
        // Create first row
        HSSFRow row = sheet.createRow(0);
        //Create first column cell
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellType(CellType.STRING);
        cell1.setCellValue("月");
        //Create second column cell
        HSSFCell cell2 = row.createCell(1);
        cell2.setCellType(CellType.STRING);
        cell2.setCellValue("出库日期");
        //Create thrid column cell
        HSSFCell cell3 = row.createCell(2);
        cell3.setCellType(CellType.STRING);
        cell3.setCellValue("出库单号");
        //Create four column cell
        HSSFCell cell4 = row.createCell(3);
        cell4.setCellType(CellType.STRING);
        cell4.setCellValue("客户");
        //Create five column cell
        HSSFCell cell5 = row.createCell(4);
        cell5.setCellType(CellType.STRING);
        cell5.setCellValue("品名");
        //Create six column cell
        HSSFCell cell6 = row.createCell(5);
        cell6.setCellType(CellType.STRING);
        cell6.setCellValue("规格");
        //Create seven column cell
        HSSFCell cell7 = row.createCell(6);
        cell7.setCellType(CellType.STRING);
        cell7.setCellValue("关联供货厂家");
        HSSFCell cell8 = row.createCell(7);
        cell8.setCellType(CellType.STRING);
        cell8.setCellValue("生产厂家");
        HSSFCell cell9 = row.createCell(8);
        cell9.setCellType(CellType.STRING);
        cell9.setCellValue("批号");
        HSSFCell cell10 = row.createCell(9);
        cell10.setCellType(CellType.STRING);
        cell10.setCellValue("有效期至");
        HSSFCell cell11 = row.createCell(10);
        cell11.setCellType(CellType.STRING);
        cell11.setCellValue("单位");
        HSSFCell cell12 = row.createCell(11);
        cell12.setCellType(CellType.STRING);
        cell12.setCellValue("数量");
        HSSFCell cell13 = row.createCell(12);
        cell13.setCellType(CellType.STRING);
        cell13.setCellValue("成本价");
        HSSFCell cell14 = row.createCell(13);
        cell14.setCellType(CellType.STRING);
        cell14.setCellValue("成本金额");
        HSSFCell cell15 = row.createCell(14);
        cell15.setCellType(CellType.STRING);
        cell15.setCellValue("开票价");
        HSSFCell cell16 = row.createCell(15);
        cell16.setCellType(CellType.STRING);
        cell16.setCellValue("开票金额");
        HSSFCell cell17 = row.createCell(16);
        cell17.setCellType(CellType.STRING);
        cell17.setCellValue("负责人");
        HSSFCell cell18 = row.createCell(17);
        cell18.setCellType(CellType.STRING);
        cell18.setCellValue("销售价");
        HSSFCell cell19 = row.createCell(18);
        cell19.setCellType(CellType.STRING);
        cell19.setCellValue("厂家返款");
        HSSFCell cell20 = row.createCell(19);
        cell20.setCellType(CellType.STRING);
        cell20.setCellValue("临床");
        ObservableList<Mother1Info> list1 = mother1.getItems();
        ObservableList<Mother2Info> list2 = mother2.getItems();
        for(int rowIndex = 0;rowIndex<list2.size();rowIndex++) {
            Mother2Info m2 = list2.get(rowIndex);
            String productName2 = m2.getProductName2() == null ? null : m2.getProductName2().trim();
            String manufacturer2 = m2.getManufacturer2() == null  ? null : m2.getManufacturer2().trim();
            String client2 = m2.getClient2() == null ? null : m2.getClient2().trim();
            HSSFRow dataRow = sheet.createRow(rowIndex+1);
            HSSFCell cellData1 = dataRow.createCell(0);
            cellData1.setCellType(CellType.STRING);
            cellData1.setCellValue(m2.getMonth2());
            //Create second column cell
            HSSFCell cellData2 = dataRow.createCell(1);
            cellData2.setCellType(CellType.STRING);
            cellData2.setCellValue(m2.getOutDate2());
            //Create thrid column cell
            HSSFCell cellData3 = dataRow.createCell(2);
            cellData3.setCellType(CellType.STRING);
            cellData3.setCellValue(m2.getOutNumber2());
            //Create four column cell
            HSSFCell cellData4 = dataRow.createCell(3);
            cellData4.setCellType(CellType.STRING);
            cellData4.setCellValue(m2.getClient2());
            //Create five column cell
            HSSFCell cellData5 = dataRow.createCell(4);
            cellData5.setCellType(CellType.STRING);
            cellData5.setCellValue(m2.getProductName2());
            //Create six column cell
            HSSFCell cellData6 = dataRow.createCell(5);
            cellData6.setCellType(CellType.STRING);
            cellData6.setCellValue(m2.getProductSpecification2());
            //Create seven column cell
            HSSFCell cellData7 = dataRow.createCell(6);
            cellData7.setCellType(CellType.STRING);
            cellData7.setCellValue(m2.getSupplier2());
            HSSFCell cellData8 = dataRow.createCell(7);
            cellData8.setCellType(CellType.STRING);
            cellData8.setCellValue(m2.getManufacturer2());
            HSSFCell cellData9 = dataRow.createCell(8);
            cellData9.setCellType(CellType.STRING);
            cellData9.setCellValue(m2.getBatchNo2());
            HSSFCell cellData10 = dataRow.createCell(9);
            cellData10.setCellType(CellType.STRING);
            cellData10.setCellValue(m2.getExpiryDate2());
            HSSFCell cellData11 = dataRow.createCell(10);
            cellData11.setCellType(CellType.STRING);
            cellData11.setCellValue(m2.getUnit2());
            HSSFCell cellData12 = dataRow.createCell(11);
            cellData12.setCellType(CellType.STRING);
            cellData12.setCellValue(m2.getQuantity2());
            HSSFCell cellData13 = dataRow.createCell(12);
            cellData13.setCellType(CellType.STRING);
            cellData13.setCellValue(m2.getCostPrice2());
            HSSFCell cellData14 = dataRow.createCell(13);
            cellData14.setCellType(CellType.STRING);
            cellData14.setCellValue(m2.getCostMoney2());
            HSSFCell cellData15 = dataRow.createCell(14);
            cellData15.setCellType(CellType.STRING);
            cellData15.setCellValue(m2.getInvoicePrice2());
            HSSFCell cellData16 = dataRow.createCell(15);
            cellData16.setCellType(CellType.STRING);
            cellData16.setCellValue(m2.getInvoiceMoney2());

            if (productName2 != null && client2 != null && manufacturer2 != null) {
                for (Mother1Info mother1Info : list1) {

                    String client1 = mother1Info.getClient1() == null ? null : mother1Info.getClient1().trim();
                    String productName1 = mother1Info.getProductName1() == null ? null: mother1Info.getProductName1().trim();
                    String manufacturer1 = mother1Info.getManufacturer1() == null ? null : mother1Info.getManufacturer1().trim();
                    if (client1 == null || productName1 == null || manufacturer1 == null) {
                        continue;
                    }
                    if(client1.equals(client2)){
                        HSSFCell cellData17 = dataRow.createCell(16);
                        cellData17.setCellType(CellType.STRING);
                        cellData17.setCellValue(mother1Info.getPrincipal1());
                    }

                    if (productName1.equals(productName2) && client1.equals(client2)  && manufacturer1.equals(manufacturer2)) {
                        HSSFCell cellData18 = dataRow.createCell(17);
                        cellData18.setCellType(CellType.STRING);
                        cellData18.setCellValue(mother1Info.getSellPrice1());
                        HSSFCell cellData19 = dataRow.createCell(18);
                        cellData19.setCellType(CellType.STRING);
                        cellData19.setCellValue(mother1Info.getRebateManufacturer1());
                        HSSFCell cellData20 = dataRow.createCell(19);
                        cellData20.setCellType(CellType.STRING);
                        cellData20.setCellValue(mother1Info.getClinical1());

                    }
                }
            }
        }
        outputExcel(fileName,workbook);
    }

    public static void outputExcel(String fileName,Workbook workbook) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(fileName));
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
